package dal.employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;

import dal.database.DBUtil;
import dal.database.DataInterface;

;
public class EmployeeUtil implements DataInterface {

	/*
	 * 
	 * +--------------------+-------------+------+-----+---------+-------+ |
	 * Field | Type | Null | Key | Default | Extra |
	 * +--------------------+-------------+------+-----+---------+-------+ |
	 * employee_id | varchar(30) | NO | PRI | | | | Name | tinytext | YES | |
	 * NULL | | | Current_Address | tinytext | YES | | NULL | | | Permnt_Address
	 * | tinytext | YES | | NULL | | | Mobile | tinytext | YES | | NULL | | |
	 * Email_id | tinytext | YES | | NULL | | | Alternate_Email_Id | tinytext |
	 * YES | | NULL | | | Designation | tinytext | YES | | NULL | | | Date_added
	 * | date | YES | | NULL | |
	 * +--------------------+-------------+------+-----+---------+-------+
	 * 
	 * 
	 * 
	 * (non-Javadoc)
	 * 
	 * @see dal.database.DataInterface#getId()
	 */
	Connection con = DBUtil.getCon();
	Statement st = null;

	String employee_id = "";

	@Override
	public String getId() {
		String employee_id="";
		String qry="select employee_id from employee";
		ResultSet rs=null;
		ArrayList<Integer> al=new ArrayList<Integer>();
		int idIntLength=5;
		try {
			
			rs=DBUtil.getCon().createStatement().executeQuery(qry);
			if(rs.next()){
				
				String tempId=rs.getString(1);
				String strIntId=tempId.substring(3, (tempId.length()));
				System.out.println("strIntId = "+strIntId);
				
				al.add(Integer.parseInt(strIntId));
				while(rs.next()){
					tempId=rs.getString(1);
					strIntId=tempId.substring(3, (tempId.length()));
					al.add(Integer.parseInt(strIntId));
				}
				
				Collections.sort(al);
				
				System.out.println("size  = "+al.size());
				
				Integer intId=al.get((al.size()-1));
				intId++;
				strIntId=intId.toString();
				System.out.println("strIntId = "+strIntId);
				int fillerLength=idIntLength-(strIntId.length());
				System.out.println("filler lnght  =  "+fillerLength);
				
				int i=0;
				String zeroString="";
				while(i<fillerLength){
					zeroString=zeroString+"0";
					i++;
				}
				
				employee_id="EMP"+zeroString+strIntId;
				
			}else{
				
				employee_id="EMP00001";
			}
			
			rs.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return employee_id;
	}

	@Override
	public boolean add(Object instance) {
		/*
		 * downcast instance to Employee class and by using getters insert
		 * values into the Employee table
		 */

		Boolean isOk = false;
		Employee employee = (Employee) instance;
		String qry = "insert into employee values(?,?,?,?,?,?,?,?,?)";
		System.out.println(qry);
		try {

			PreparedStatement preparedStatement = DBUtil.getCon()
					.prepareStatement(qry);

			preparedStatement.setString(1, employee.getEmployeeId());
			preparedStatement.setString(2, employee.getName());
			preparedStatement.setString(3, employee.getCurrentAddress());
			preparedStatement.setString(4, employee.getPermenentAddress());
			preparedStatement.setString(5, employee.getMobile());
			preparedStatement.setString(6, employee.getEmailId());
			preparedStatement.setString(7, employee.getAlternateEmailId());
			preparedStatement.setString(8, employee.getDesignation());
			preparedStatement.setDate(9, employee.getDateAdded());

			System.out.println("serv  =  " + employee.getEmployeeId());

			int res = preparedStatement.executeUpdate();
			if (res > 0) {
				isOk = true;
			} else {
				isOk = false;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return isOk;
	}

	@Override
	public boolean update(Object instance) {
		/*
		 * downcast instance to Employee class and by using getters update all
		 * fields of table by id into the Employee table
		 */

		Boolean isOk = false;
		Employee employee = (Employee) instance;
		String qry = "update employee set Name = ?,Current_Address= ?,Permnt_Address= ?,Mobile= ?,Email_id= ?,Alternative_Email_id= ?,Designation= ?,Date_Added= ? where employee_id = ?";
		System.out.println(qry);
		try {
			PreparedStatement preparedStatement = DBUtil.getCon()
					.prepareStatement(qry);
			preparedStatement.setString(1, employee.getName());
			preparedStatement.setString(2, employee.getCurrentAddress());
			preparedStatement.setString(3, employee.getPermenentAddress());
			preparedStatement.setString(4, employee.getMobile());
			preparedStatement.setString(5, employee.getEmailId());
			preparedStatement.setString(6, employee.getAlternateEmailId());
			preparedStatement.setString(7, employee.getDesignation());
			preparedStatement.setDate(8, employee.getDateAdded());
			preparedStatement.setString(9, employee.getEmployeeId());
			System.out.println(employee.getName());
			int res = preparedStatement.executeUpdate();
			if (res > 0) {
				isOk = true;
			} else {
				isOk = false;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return isOk;
	}

	@Override
	public ArrayList<Object> get() {
		/*
		 * prepare list of for the all record of Employee table
		 */
		ResultSet rs = null;

		ArrayList<Object> al = new ArrayList<Object>();
		String qry = "select * from employee";
		System.out.println(qry);
		try {
			st = con.createStatement();
			rs = st.executeQuery(qry);

			while (rs.next()) {
				Employee employee = new Employee();
				
				employee.setEmployeeId(rs.getString(1));
				employee.setName(rs.getString(2));
				employee.setCurrentAddress(rs.getString(3));
				employee.setPermenentAddress(rs.getString(4));
				employee.setMobile(rs.getString(5));
				employee.setEmailId(rs.getString(6));
				employee.setAlternateEmailId(rs.getString(7));
				employee.setDesignation(rs.getString(8));
				employee.setDateAdded(rs.getDate(9));

				al.add(employee);
				System.out.println(al);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				throw e;
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return al;
	}

	@Override
	public boolean delete(String id) {

		// do delete for the given id

		Boolean isOk = false;

		Employee employee = new Employee();

		String qry = "DELETE FROM employee WHERE employee_id = ?";
		System.out.println(qry);
		System.out.println(id);

		try {

			PreparedStatement preparedStatement = DBUtil.getCon()
					.prepareStatement(qry);

			preparedStatement.setString(1, id);
			System.out.println();

			int res = preparedStatement.executeUpdate();
			System.out.println(res);
			if (res == 1) {
				isOk = true;

			} else {
				isOk = false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Exception from delete");
		}

		return isOk;
	}

	@Override
	public Object get(String id) {
		// TODO Auto-generated method stub

		/*
		 * return record of Employee whose id is in the "id" , return only
		 * single instance of Employee ;
		 */
		ResultSet rs = null;

		System.out.println(id);
		String qry = "Select * from employee WHERE employee_id=?";
		System.out.println(qry);

		Employee employee = new Employee();
		try {
			PreparedStatement preparedStatement = DBUtil.getCon()
					.prepareStatement(qry);
			preparedStatement.setString(1, id);
			System.out.println(id);
			rs = preparedStatement.executeQuery();
			if (rs.next()) {

				String s1 = rs.getString(1);
				String s2 = rs.getString(2);
				String s3 = rs.getString(3);
				String s4 = rs.getString(4);
				String s5 = rs.getString(5);
				String s6 = rs.getString(6);
				String s7 = rs.getString(7);
				String s8 = rs.getString(8);
				String s9 = rs.getString(9);

				employee.setEmployeeId(rs.getString(1));
				employee.setName(rs.getString(2));
				employee.setCurrentAddress(rs.getString(3));
				employee.setPermenentAddress(rs.getString(4));
				employee.setMobile(rs.getString(5));
				employee.setEmailId(rs.getString(6));
				employee.setAlternateEmailId(rs.getString(7));
				employee.setDesignation(rs.getString(8));
				employee.setDateAdded(rs.getDate(9));

				System.out.println(s1 + "\t" + s2 + "\t" + s3 + "\t" + s4
						+ "\t" + s5 + "\t" + s6 + "\t" + s7 + "\t" + s8 + "\t"
						+ s9);
				System.out.println("\n" + employee);

			} else {
				employee=null;
				System.out.println("No data in table"+employee);
			}

		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		return employee;
	}

}
